package com.jiyinit.web.core.common.util;

import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;

/**
 * 导出Excel公共方法 (poi-version: 3.11)
 * @version 1.0
 * @author txd
 */
public class ExportExcelUtil {

    /**
     * 显示的导出表的标题
     */
    private String title = "统计数据";
    /**
     * 导出表的列名
     */
    private String[] rowNames;
    /**
     * 数据集合, 其中obj数组表示每一列的值
     */
    private List<Object[]> dataList = new ArrayList<Object[]>();
    /**
     * 是否展示序号<br>
     * 默认展示
     */
    private boolean showNum = true;
    /**
     * 用于输出流
     */
    HttpServletResponse response;
    /**
     * 标准列宽<br>
     * 当列宽超过标准列宽时, 使用标准宽度,防止报错
     */
    private int textWidth = 28;

    /**
     * 构造函数
     */
    public ExportExcelUtil(){}
    public ExportExcelUtil(String title, String[] rowNames, List<Object[]> dataList, boolean showNum,HttpServletResponse response) {
        this.dataList = dataList;
        this.rowNames = rowNames;
        this.title = title;
        this.response = response;
        this.showNum = showNum;
    }

    /**
     * 导出数据
     */
    public void export() throws Exception {

        HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
        // sheet样式定义  getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可自行定义
        HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);// 获取列头样式对象
        HSSFCellStyle style = this.getStyle(workbook); // 单元格样式对象

        HSSFSheet sheet = workbook.createSheet(title); // 创建工作表

        // 产生表格标题行
        HSSFRow rowm = sheet.createRow(0);
        HSSFCell cellTiltle = rowm.createCell(0);
        if(showNum == true){
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, rowNames.length));
        }else{
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowNames.length - 1)));
        }
        cellTiltle.setCellStyle(columnTopStyle);
        cellTiltle.setCellValue(title);

        int totalColNum;        //定义所需列数
        totalColNum = generateRowHead(sheet, columnTopStyle);   //生成列头

        // 将查询出的数据设置到sheet对应的单元格中
        for (int i = 0; i < dataList.size(); i++) {
            Object[] objs = dataList.get(i);                     //遍历每个对象
            generateRows(objs, i + 3, totalColNum, sheet, style);//生成行并赋值
        }

        // 让列宽随着导出的列长自动适应
        for (int colNum = 0; colNum < totalColNum; colNum++) {
            int columnWidth = sheet.getColumnWidth(colNum) / 256;   //获取列宽
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                HSSFRow currentRow;
                // 当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
                if (currentRow.getCell(colNum) != null) {
                    HSSFCell currentCell = currentRow.getCell(colNum);
                    if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            if (colNum == 0) {
                sheet.setColumnWidth(colNum, (columnWidth - 4) * 256);
            } else {
                int width = (columnWidth + 4) * 256;
                if (columnWidth < textWidth) {
                    sheet.setColumnWidth(colNum, width);
                } else {
                    sheet.setColumnWidth(colNum, (textWidth + 4) * 256);
                }
            }
        }

        if (workbook != null) {
            OutputStream out = response.getOutputStream();
            try {
                String fileName = "Excel-"
                        + String.valueOf(System.currentTimeMillis())
                        .substring(4, 13) + ".xls";
                String headStr = "attachment; filename=\"" + fileName
                        + "\"";
                response.setContentType("APPLICATION/OCTET-STREAM");
                response.setHeader("Content-Disposition", headStr);
                workbook.write(out);
            } catch (Exception e){
                e.printStackTrace();
            }finally {
                out.close();
                workbook.close();
            }
        }
    }

    /*
     * 列头单元格样式
     */
    public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {

        // 设置字体
        HSSFFont font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 11);
        // 字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;
    }

    /*
     * 列数据信息单元格样式
     */
    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        // 设置字体大小
        // font.setFontHeightInPoints((short)10);
        // 字体加粗
        // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;
    }

    /**
     * 生成表头
     * @param sheet
     * @param style
     * @return
     */
    public int generateRowHead(HSSFSheet sheet, HSSFCellStyle style){
        int columnNum;      //表头列数
        HSSFRow row = sheet.createRow(2);    //在索引2的位置创建行(最顶端的行开始的第二行)
        //将列头设置到sheet的单元格中
        if(showNum == true){
            columnNum = (rowNames.length + 1);
            for (int i = 0; i < columnNum; i++) {
                if(i > 0){
                    setCellValue(rowNames[i-1], i, row, sheet, style);
                }else{
                    setCellValue("序号", i, row, sheet, style);
                }
            }
        }else{
            columnNum = rowNames.length;
            for (int i = 0; i < columnNum; i++) {
                setCellValue(rowNames[i], i, row, sheet, style);
            }
        }
        return columnNum;
    }

    /**
     * 生成数据列
     * @param objs
     * @param rowOrder
     * @param total
     * @param sheet
     * @param style
     */
    public void generateRows(Object[] objs, int rowOrder, int total, HSSFSheet sheet, HSSFCellStyle style){
        HSSFRow row = sheet.createRow(rowOrder);
        if(showNum == true){
            for (int i = 0; i < total; i++) {
                if(i > 0){
                    Object obj = objs[i-1];
                    if (!"".equals(obj) && obj != null) {
                        setCellValue(obj.toString(), i, row, sheet, style);
                    } else {
                        setCellValue("", i, row, sheet, style);
                    }
                } else {
                    setCellValue(rowOrder - 2, i, row, sheet, style);
                }
            }
        }else{
            for (int i = 0; i < total; i++) {
                Object obj = objs[i];
                if (!"".equals(obj) && obj != null) {
                    setCellValue(obj.toString(), i, row, sheet, style);
                } else {
                    setCellValue("", i, row, sheet, style);
                }
            }
        }
    }

    /**
     * 为列赋值(string)
     * @param value
     * @param i
     * @param row
     * @param sheet
     * @param style
     */
    public void setCellValue(String value, int i, HSSFRow row, HSSFSheet sheet, HSSFCellStyle style) {
        HSSFCell cell = row.createCell(i);               //创建列头对应个数的单元格
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);     //设置列头单元格的数据类型
        //HSSFRichTextString text = new HSSFRichTextString(value);
        cell.setCellValue(value);                         //设置列头单元格的值
        cell.setCellStyle(style);                        //设置列头单元格样式
    }

    /**
     * 为列赋值(int)
     * @param value
     * @param i
     * @param row
     * @param sheet
     * @param style
     */
    public void setCellValue(int value, int i, HSSFRow row, HSSFSheet sheet, HSSFCellStyle style){
        HSSFCell cell = row.createCell(i);               //创建列头对应个数的单元格
        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);     //设置列头单元格的数据类型
        //HSSFRichTextString text = new HSSFRichTextString(value);
        cell.setCellValue(value);                         //设置列头单元格的值
        cell.setCellStyle(style);                        //设置列头单元格样式
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String[] getRowNames() {
        return rowNames;
    }

    public void setRowNames(String[] rowNames) {
        this.rowNames = rowNames;
    }

    public List<Object[]> getDataList() {
        return dataList;
    }

    public void setDataList(List<Object[]> dataList) {
        this.dataList = dataList;
    }

    public boolean isShowNum() {
        return showNum;
    }

    public void setShowNum(boolean showNum) {
        this.showNum = showNum;
    }

    public int getTextWidth() {
        return textWidth;
    }

    public void setTextWidth(int textWidth) {
        this.textWidth = textWidth;
    }

}